package main

import (
	"Database/model"
	"errors"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
	"github.com/jmoiron/sqlx"
	"strings"
)

var db *sqlx.DB
var err error

func initDB() {
	dsn := "root:109456@tcp(127.0.0.1:3306)/mysqlgo"
	db, err = sqlx.Connect("mysql", dsn)
	if err != nil {
		fmt.Printf("connect DB failed, err:%v\n", err)
		return
	}
	//db.SetMaxOpenConns(20)
	//db.SetMaxIdleConns(10)

	return
}

func main() {
	initDB()
	defer db.Close()
	//namequery()
	u1 := model.User{Name: "zxc", Age: 18}
	u2 := model.User{Name: "qwe", Age: 28}
	u3 := model.User{Name: "asd", Age: 38}
	//方法1
	users := []*model.User{&u1, &u2, &u3}
	err = batchInsertUser(users)
	if err != nil {
		fmt.Printf("BatchInsertUsers failed, err:%v\n", err)
	}
	// 方法2
	users2 := []interface{}{u1, u2, u3}
	err = batchInsertUser2(users2)
	if err != nil {
		fmt.Printf("BatchInsertUsers2 failed, err:%v\n", err)
	}

	//方法3
	batchinsertuser3(users)

	QueryByIDs([]int{1, 2, 3})
}

func queryDemo() {
	sql := "select id ,name ,age from user where id=?"
	var u model.User
	err = db.Get(&u, sql, 1)
	if err != nil {
		fmt.Printf("get failed, err:%v\n", err)
		return
	}
	fmt.Printf("id:%d name:%s age:%d\n", u.Id, u.Name, u.Age)
}
func queryMultiDemo() {
	sql := "select id,name,age from user where id>?"
	var u []model.User
	err := db.Select(&u, sql, 0)

	if err != nil {
		fmt.Printf("query failed, err:%v\n", err)
		return
	}
	fmt.Printf("users:%#v\n", u)
}

// 插入数据
func insertRowDemo() {
	sqlStr := "insert into user(name, age) values (?,?)"
	ret, err := db.Exec(sqlStr, "ybl2", 19)
	if err != nil {
		fmt.Printf("insert failed, err:%v\n", err)
		return
	}
	theID, err := ret.LastInsertId() // 新插入数据的id
	if err != nil {
		fmt.Printf("get lastinsert ID failed, err:%v\n", err)
		return
	}
	fmt.Printf("insert success, the id is %d.\n", theID)
}

// 更新数据
func updateRowDemo() {
	sqlStr := "update user set age=? where id = ?"
	ret, err := db.Exec(sqlStr, 39, 6)
	if err != nil {
		fmt.Printf("update failed, err:%v\n", err)
		return
	}
	n, err := ret.RowsAffected() // 操作影响的行数
	if err != nil {
		fmt.Printf("get RowsAffected failed, err:%v\n", err)
		return
	}
	fmt.Printf("update success, affected rows:%d\n", n)
}

// 删除数据
func deleteRowDemo() {
	sqlStr := "delete from user where id = ?"
	ret, err := db.Exec(sqlStr, 6)
	if err != nil {
		fmt.Printf("delete failed, err:%v\n", err)
		return
	}
	n, err := ret.RowsAffected() // 操作影响的行数
	if err != nil {
		fmt.Printf("get RowsAffected failed, err:%v\n", err)
		return
	}
	fmt.Printf("delete success, affected rows:%d\n", n)
}
func inserrUserDemo() {
	sql := "insert into user(name,age) values(:name,:age)"
	_, err = db.NamedExec(sql, map[string]interface{}{
		"name": "xxxx",
		"age":  12,
	})
}
func namequery() {
	sql := "select *from user where name=:name"
	rows, err := db.NamedQuery(sql, map[string]interface{}{
		"name": "ybl",
	})
	if err != nil {
		fmt.Printf("db.NamedQuery failed, err:%v\n", err)
		return
	}
	defer rows.Close()
	for rows.Next() {
		var u model.User
		err := rows.StructScan(&u)
		if err != nil {
			fmt.Printf("scan failed, err:%v\n", err)
			continue
		}
		fmt.Printf("user:%#v\n", u)
	}
	u := model.User{Name: "ybl"}
	rows, err = db.NamedQuery(sql, u)
	if err != nil {
		fmt.Printf("db.NamedQuery failed, err:%v\n", err)
		return
	}
	defer rows.Close()
	for rows.Next() {
		var u model.User
		err := rows.StructScan(&u)
		if err != nil {
			fmt.Printf("scan failed, err:%v\n", err)
			continue
		}
		fmt.Printf("user:%#v\n", u)
	}
}
func transactionDemo() error {
	tx, err := db.Beginx()
	if err != nil {
		fmt.Printf("begin trans failed, err:%v\n", err)
		return err
	}
	defer func() {
		if p := recover(); p != nil {
			tx.Rollback()
			panic(p) // re-throw panic after Rollback
		} else if err != nil {
			fmt.Println("rollback")
			tx.Rollback() // err is non-nil; don't change it
		} else {
			err = tx.Commit() // err is nil; if Commit returns error update err
			fmt.Println("commit")
		}
	}()
	sqlStr1 := "Update user set age=20 where id=?"

	rs, err := tx.Exec(sqlStr1, 1)
	if err != nil {
		return err
	}
	n, err := rs.RowsAffected()
	if err != nil {
		return err
	}
	if n != 1 {
		return errors.New("exec sqlStr1 failed")
	}
	sqlStr2 := "Update user set age=50 where i=?"
	rs, err = tx.Exec(sqlStr2, 5)
	if err != nil {
		return err
	}
	n, err = rs.RowsAffected()
	if err != nil {
		return err
	}
	if n != 1 {
		return errors.New("exec sqlStr1 failed")
	}
	return err
}

//批量插入
func batchInsertUser(users []*model.User) error {
	valuesStrings := make([]string, 0, len(users))
	valueArgs := make([]interface{}, 0, len(users)*2)
	for _, u := range users {
		valuesStrings = append(valuesStrings, "(?,?)")
		valueArgs = append(valueArgs, u.Name, u.Age)
	}
	stmt := fmt.Sprintf("INSERT INTO user (name, age) VALUES %s", strings.Join(valuesStrings, ","))
	_, err := db.Exec(stmt, valueArgs...)
	return err
}
func batchInsertUser2(users []interface{}) error {
	//三个问号？ 代表要插入三条记录 ，少或者多一个? 都不行，必须和你要插入的数量对等 挺傻逼
	query, args, _ := sqlx.In("insert into user (name,age) values (?),(?),(?)", users...)
	fmt.Println(query) // 查看生成的querystring
	fmt.Println(args)  // 查看生成的args
	_, err := db.Exec(query, args...)
	return err
}
func batchinsertuser3(user []*model.User) error {
	_, err := db.NamedExec("insert into user(name,age) values (:name,:age)", user)
	return err
}

// QueryByIDs 根据给定ID查询
func QueryByIDs(ids []int) (users []model.User, err error) {
	// 动态填充id
	query, args, err := sqlx.In("SELECT name, age FROM user WHERE id IN (?)", ids)
	if err != nil {
		return
	}
	// sqlx.In 返回带 `?` bindvar的查询语句, 我们使用Rebind()重新绑定它
	fmt.Println(query)
	query = db.Rebind(query)
	fmt.Println(query)
	err = db.Select(&users, query, args...)
	return
}

// QueryAndOrderByIDs 按照指定id查询并维护顺序
func QueryAndOrderByIDs(ids []int) (users []model.User, err error) {
	// 动态填充id
	strIDs := make([]string, 0, len(ids))
	for _, id := range ids {
		strIDs = append(strIDs, fmt.Sprintf("%d", id))
	}
	query, args, err := sqlx.In("SELECT name, age FROM user WHERE id IN (?) ORDER BY FIND_IN_SET(id, ?)", ids, strings.Join(strIDs, ","))
	if err != nil {
		return
	}

	// sqlx.In 返回带 `?` bindvar的查询语句, 我们使用Rebind()重新绑定它
	query = db.Rebind(query)

	err = db.Select(&users, query, args...)
	return
}
